package com.atguigu.gamll_sugar.mapper;

import com.atguigu.gamll_sugar.entity.C3Stats;
import com.atguigu.gamll_sugar.entity.SpuStats;
import com.atguigu.gamll_sugar.entity.TmStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author chenlongStart
 * @create 2021-07-07 13:01
 * @desc
 */

public interface ProductStatsMapper {

    @Select(value = "select sum(order_amount) from product_stats_2021 where toYYYYMMDD(stt)=#{date}")
    BigDecimal getGMV(@Param("date") int date);

    @Select("SELECT \n" +
            "    tm_name,\n" +
            "    sum(order_amount) AS order_amount\n" +
            "FROM product_stats_2021\n" +
            "WHERE toYYYYMMDD(stt) = #{date}\n" +
            "GROUP BY tm_name\n" +
            "ORDER BY order_amount DESC\n" +
            "LIMIT #{limit}")
    List<TmStats> getTmGMV(@Param("date") int date,
                           @Param("limit") int limit);

    @Select("SELECT \n" +
            "    category3_name,\n" +
            "    sum(order_amount) AS order_amount\n" +
            "FROM product_stats_2021\n" +
            "WHERE toYYYYMMDD(stt) = #{date}\n" +
            "GROUP BY category3_name\n" +
            "ORDER BY order_amount DESC\n" +
            "LIMIT #{limit}")
    List<C3Stats> getC3GMV(@Param("date")int date,@Param("limit") int limit);


    @Select("SELECT \n" +
            "    spu_name,\n" +
            "    sum(order_amount) AS order_amount\n" +
            "FROM product_stats_2021\n" +
            "WHERE toYYYYMMDD(stt) = #{date}\n" +
            "GROUP BY spu_name\n" +
            "ORDER BY order_amount DESC\n" +
            "LIMIT #{limit}")
    List<SpuStats> getSpuGMV(@Param("date")int date,@Param("limit") int limit);
}
